package com.zxwl.base.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.orm.hibernate3.SessionFactoryUtils;

public class ProcedureSqlServer<T>  implements IProcedure<T>
{
	protected EntityHelper<T> entityHelper;
	private HibernateTemplate hibernateTemplate;
	
	public ProcedureSqlServer(EntityHelper<T> entityHelper)
	{
		this.entityHelper = entityHelper;
	}
	
	public String formatFieldName(String  fieldName)
	{
		StringBuilder nameBuffer = new StringBuilder();
		if(fieldName.indexOf("_") > -1){
			fieldName = fieldName.toLowerCase();
			String names[] = fieldName.split("_"); 
			nameBuffer.append(names[0]);
			for(int i = 1 ;i < names.length; i ++)
			{
				nameBuffer.append(names[i].substring(0,1).toUpperCase());
				nameBuffer.append(names[i].substring(1));
			}
		}else{
			nameBuffer.append(fieldName);
		}
		
		return nameBuffer.toString();
	}
	@Override
	public void setHibernateTemplate(HibernateTemplate hibernateTemplate)
	{
		this.hibernateTemplate = hibernateTemplate;
	}

	public HibernateTemplate getHibernateTemplate()
	{
		return hibernateTemplate;
	}
	
	public int getDatabaseType(Class<?> type)
	{
		if(type == String.class)
		{
			return java.sql.Types.VARCHAR;
		}
		else if(type == Integer.class )
		{
			return java.sql.Types.INTEGER;
		}
		else if(type == Short.class)
		{
			return java.sql.Types.INTEGER;
		}
		else if(type == Date.class)
		{
			return java.sql.Types.DATE;
		}
		return java.sql.Types.VARCHAR;
	}
	
	public Object convertValue(Object value)
	{
		if(null == value)
		{
			return null;
		}
		
		Class<?> type = value.getClass();
		if(type == String.class)
		{
			return value;
		}
		else if(type == Integer.class )
		{
			return value;
		}
		else if(type == Short.class)
		{
			return value;
		}
		else if(type == java.util.Date.class)
		{
			return  new java.sql.Date(((Date)value).getTime());
		}
		return value;
		
	}
	
	public void registerParameter(CallableStatement cs,QueryParameter queryParameter) throws SQLException
	{
		Set<String> paraNames;
		paraNames = queryParameter.findParameterNames();
		for (String paraName : paraNames)
		{
			Parameter parameter = queryParameter.findParameter(paraName);
			
			if (null != parameter)
			{
				int paraType = getDatabaseType(parameter.findType());
				Object value = parameter.getValue();
				if(parameter.getDirction().equals("IN"))
				{
					cs.setObject(paraName, convertValue(value),paraType);
				}
				else
				{
					cs.registerOutParameter(paraName,paraType);
					if(value != null)
					{
						cs.setObject(paraName, convertValue(value),paraType);
					}
				}
			}
			
		}
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public List<T> queryList(final String procedureName,final QueryParameter queryParameter)
	{
		return this.getHibernateTemplate().execute(new HibernateCallback()
		{
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException
			{
				Connection con = SessionFactoryUtils.getDataSource(session.getSessionFactory()).getConnection();
				
				String queryString = "";
				String queryParams = "";
								
				int paramCount = 0;
				paramCount = queryParameter.size();

				for(int i = 0; i < paramCount; i ++)
				{
					queryParams += "?,";
				}
				
				if(!"".equals(queryParams))
				{
					queryParams = queryParams.substring(0,queryParams.length() - 1);
				}
				
				queryString = "{call " + procedureName + "(" + queryParams + ")}" ;
				CallableStatement cs = con.prepareCall(queryString);
				Set<String> paraNames;
				paraNames = queryParameter.findParameterNames();
				List<T> resultList = null;
				ResultSet rs = null;
				try
				{
					// 注册参数
					registerParameter(cs,queryParameter);
					// 执行存储过程
					rs = cs.executeQuery();
					
					if(entityHelper == null)
					{
						throw new Exception("entityHelper异常");
					}
					resultList = entityHelper.convert(rs);
					
					// 读取出参值
					for (String paraName : paraNames)
					{
						Parameter parameter = queryParameter.findParameter(paraName);
						if (null != parameter)
						{
							if(parameter.getDirction().equals("OUT"))
							{
								parameter.setValue(cs.getObject(paraName));
							}
						}
					}
				}
				catch (SQLException e)
				{
					e.printStackTrace();
				}
				catch(Exception e)
				{
					e.printStackTrace();
				}
				finally
				{
					if(null != rs)
					{
						rs.close();
					}
					if(null != cs)
					{
						cs.close();
					}
					if(null != con)
					{
						con.close();
					}
				}
				return resultList;
			}
		});
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public void execute(final String procedureName, final QueryParameter queryParameter)
	{
		this.getHibernateTemplate().execute(new HibernateCallback()
		{
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException
			{
				Connection con = SessionFactoryUtils.getDataSource(session.getSessionFactory()).getConnection();
				
				String queryString = "";
				String queryParams = "";
								
				int paramCount = 0;
				paramCount = queryParameter.size();
				
				for(int i = 0; i < paramCount; i ++)
				{
					queryParams += "?,";
				}
				
				if(!"".equals(queryParams))
				{
					queryParams = queryParams.substring(0,queryParams.length() - 1);
				}
				
				queryString = "{call " + procedureName + "(" + queryParams + ")}" ;
				
				CallableStatement cs = con.prepareCall(queryString);
				Set<String> paraNames;
				// 注册参数
				paraNames = queryParameter.findParameterNames();
				try
				{
					// 注册参数
					registerParameter(cs,queryParameter);
					// 执行存储过程
					cs.execute();
					// 读取出参值
					for (String paraName : paraNames)
					{
						Parameter parameter = queryParameter.findParameter(paraName);
						if (null != parameter)
						{
							if(parameter.getDirction().equals("OUT"))
							{
								parameter.setValue(cs.getObject(paraName));
							}
						}
					}
				}
				catch(Exception e)
				{
					e.printStackTrace();
				}
				finally
				{
					if(null != cs)
					{
						cs.close();
					}
					if(null != con)
					{
						con.close();
					}
				}
				return null;
			}
		});
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public List<Map<String, String>> queryMapList(final String procedureName,final QueryParameter  queryParameter)
	{
		return this.getHibernateTemplate().execute(new HibernateCallback()
		{
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException
			{
				Connection con = SessionFactoryUtils.getDataSource(session.getSessionFactory()).getConnection();
				
				
				String queryString = "";
				String queryParams = "";
								
				int paramCount = 0;
				paramCount = queryParameter.size();

				for(int i = 0; i < paramCount; i ++)
				{
					queryParams += "?,";
				}
				
				if(!"".equals(queryParams))
				{
					queryParams = queryParams.substring(0,queryParams.length() - 1);
				}
				
				queryString = "{call " + procedureName + "(" + queryParams + ")}" ;
				
				CallableStatement cs = con.prepareCall(queryString);
				Set<String> paraNames;
				// 注册参数
				paraNames = queryParameter.findParameterNames();
				// 注册输出参数的类型
				List<Map<String,Object>> resultList = null;
				ResultSet rs = null;
				try
				{
					// 注册参数
					registerParameter(cs,queryParameter);
					// 执行存储过程
					rs  = cs.executeQuery();
					
					// 读取游标参数
					int colCount = rs.getMetaData().getColumnCount();
					String colNameList[] = new String[colCount];
					for (int i = 0; i < colCount; i++)
					{
						String fieldName = rs.getMetaData().getColumnName(i + 1);
						
//						colNameList[i] = formatFieldName(fieldName.toLowerCase());
						colNameList[i] = formatFieldName(fieldName);
					}
					
					resultList = new ArrayList();

					while (rs.next())
					{
						Map rowMap = new HashMap();
						for (int i = 0; i < colCount; i++)
						{
							Object val = rs.getObject(i + 1);
							if (null != val)
							{
								if (val instanceof java.sql.Date)
								{
									val = rs.getTimestamp(i + 1);
									rowMap.put(colNameList[i], val.toString());
								}
								else
								{
									rowMap.put(colNameList[i], val.toString());
								}
							}
							else
							{
								rowMap.put(colNameList[i], null);
							}

						}
						resultList.add(rowMap);
					}
					// 读取出参值
					for (String paraName : paraNames)
					{
						Parameter parameter = queryParameter.findParameter(paraName);
						if (null != parameter)
						{
							if(parameter.getDirction().equals("OUT"))
							{
								parameter.setValue(cs.getObject(paraName));
							}
						}
					}
					
				}
				catch(Exception e)
				{
					e.printStackTrace();
				}
				finally
				{
					if(null != rs)
					{
						rs.close();
					}
					if(null != cs)
					{
						cs.close();
					}
					if(null != con)
					{
						con.close();
					}
				}
				return resultList;
			}
		});
	}
}
